﻿using QuanLyThuVien.DAL;
using QuanLyThuVien.DTO;
using QuanLyThuVien.Supporter;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace QuanLyThuVien.DAL
{
    //----------------------------------------------------------------------
    //Desc: class kết nối với bảng DAUSACH
    //----------------------------------------------------------------------
    public class DALDauSach
    {
        //----------------------------------------------------------------------
        //Desc: constructor mặc định
        //----------------------------------------------------------------------
        public DALDauSach()
        { }

        //----------------------------------------------------------------------
        //Desc: chuyển dòng dữ liệu thành đầu sách
        //----------------------------------------------------------------------
        public DTODauSach ConvertToDauSach(DataRow row)
        {
            DTODauSach dausach = new DTODauSach();
            dausach.MaDauSach = row["MaDauSach"].ToString();
            dausach.TenDauSach = row["TenDauSach"].ToString();
            dausach.TheLoai.MaTheLoai = row["MaTL"].ToString();

            return dausach;
        }

        //----------------------------------------------------------------------
        //Desc: lấy danh sách đầu sách
        //----------------------------------------------------------------------
        public List<DTODauSach> LayDSDauSach()
        {
           if (DatabaseManager.DbConnection == null)
                return null;
            string spName = "spDauSach_LayDanhSachDauSach";
            DataTable data = DatabaseManager.DbConnection.ExecuteStoredProcedure(spName);
            if (data == null)
                return null;
            List<DTODauSach> list = new List<DTODauSach>();
            foreach (DataRow row in data.Rows)
                list.Add(ConvertToDauSach(row));
            return list;
        }

        //----------------------------------------------------------------------
        //Desc: lấy đầu sách
        //----------------------------------------------------------------------
        public DTODauSach LayDauSach(string madausach)
        {
           if (DatabaseManager.DbConnection == null)
                return null;
            string spName = "spDauSach_LayDauSach";
            SqlParameter sqlprMaDauSach = new SqlParameter("@MaDauSach", SqlDbType.VarChar, 15) { Value = madausach };
            DataTable data = DatabaseManager.DbConnection.ExecuteStoredProcedure(spName, sqlprMaDauSach);
            if (data == null || data.Rows.Count == 0)
                return null;
            return ConvertToDauSach(data.Rows[0]);
        }

        //----------------------------------------------------------------------
        //Desc: đếm đầu sách
        //----------------------------------------------------------------------
        public int DemDauSach()
        {
           if (DatabaseManager.DbConnection == null)
                return -1;
            string spName = "spDauSach_DemDauSach";
            object obj = DatabaseManager.DbConnection.ExecuteStoredProcedureScalar(spName);
            if (obj == null)
                return -1;
            int dem;
            try
            {
                dem = (int)obj;
            }
            catch { return -1; }

            return dem;
        }

        //----------------------------------------------------------------------
        //Desc: đếm mã đầu sách
        //----------------------------------------------------------------------
        public int DemMaDauSach(string madausach)
        {
           if (DatabaseManager.DbConnection == null)
                return -1;
            string spName = "spDauSach_DemMaDauSach";
            SqlParameter sqlprMaDauSach = new SqlParameter("@MaDauSach", SqlDbType.VarChar, 15) { Value = madausach };
            object obj = DatabaseManager.DbConnection.ExecuteStoredProcedureScalar(spName, sqlprMaDauSach);
            if (obj == null)
                return -1;
            int dem;
            try
            {
                dem = (int)obj;
            }
            catch { return -1; }

            return dem;
        }

        //----------------------------------------------------------------------
        //Desc: đếm số sách của đầu sách
        //----------------------------------------------------------------------
        public int DemSoSach(string madausach)
        {
           if (DatabaseManager.DbConnection == null)
                return -1;
            string spName = "spDauSach_DemSoSach";
            SqlParameter sqlprMaDauSach = new SqlParameter("@MaDauSach", SqlDbType.VarChar, 15) { Value = madausach };
            object obj = DatabaseManager.DbConnection.ExecuteStoredProcedureScalar(spName, sqlprMaDauSach);
            if (obj == null)
                return -1;
            int dem;
            try
            {
                dem = (int)obj;
            }
            catch { return -1; }

            return dem;
        }

        //----------------------------------------------------------------------
        //Desc: lấy tên thể loại
        //----------------------------------------------------------------------
        public string LayTenTL(string madausach)
        {
           if (DatabaseManager.DbConnection == null)
                return null;
            string spName = "spDauSach_LayTenTL";
            SqlParameter sqlprMaDauSach = new SqlParameter("@MaDauSach", SqlDbType.VarChar, 15) { Value = madausach };
            DataTable data = DatabaseManager.DbConnection.ExecuteStoredProcedure(spName, sqlprMaDauSach);
            if (data == null || data.Rows.Count == 0)
                return null;
            return data.Rows[0]["TenTheLoai"].ToString();
        }

        //----------------------------------------------------------------------
        //Desc: lấy danh sách tên tác giả
        //----------------------------------------------------------------------
        public List<string> LayDSTenTG(string madausach)
        {
           if (DatabaseManager.DbConnection == null)
                return null;
            string spName = "spDauSach_LayDanhSachTenTG";
            SqlParameter sqlprMaDauSach = new SqlParameter("@MaDauSach", SqlDbType.VarChar, 15) { Value = madausach };
            DataTable data = DatabaseManager.DbConnection.ExecuteStoredProcedure(spName, sqlprMaDauSach);
            if (data == null)
                return null;
            List<string> list = new List<string>();
            for (int i = 0; i < data.Rows.Count; i++)
                list.Add(data.Rows[i]["TenTacGia"].ToString());
            return list;
        }

        //----------------------------------------------------------------------
        //Desc: cập nhật đầu sách
        //----------------------------------------------------------------------
        public bool CapNhat(DTODauSach dausach)
        {
           if (DatabaseManager.DbConnection == null)
                return false;
            string spName = "spDauSach_CapNhatDauSach";
            SqlParameter sqlprThaoTac = new SqlParameter("@ThaoTac", SqlDbType.Int) { Value = 1 };
            SqlParameter sqlprMaDauSach = new SqlParameter("@MaDauSach", SqlDbType.VarChar, 15) { Value = dausach.MaDauSach };
            SqlParameter sqlprTenDauSach = new SqlParameter("@TenDauSach", SqlDbType.NVarChar, 70) { Value = dausach.TenDauSach };
            SqlParameter sqlprMaTL = new SqlParameter("@MaTL", SqlDbType.VarChar, 15) { Value = dausach.TheLoai.MaTheLoai };
            return DatabaseManager.DbConnection.ExecuteStoredProcedureNonQuery(spName, sqlprThaoTac, sqlprMaDauSach, sqlprTenDauSach, sqlprMaTL);
        }

        //----------------------------------------------------------------------
        //Desc: xóa đầu sách
        //----------------------------------------------------------------------
        public bool Xoa(DTODauSach dausach)
        {
           if (DatabaseManager.DbConnection == null)
                return false;
            string spName = "spDauSach_CapNhatDauSach";
            SqlParameter sqlprThaoTac = new SqlParameter("@ThaoTac", SqlDbType.Int) { Value = 0 };
            SqlParameter sqlprMaDauSach = new SqlParameter("@MaDauSach", SqlDbType.VarChar, 15) { Value = dausach.MaDauSach };
            SqlParameter sqlprTenDauSach = new SqlParameter("@TenDauSach", SqlDbType.NVarChar, 70) { Value = dausach.TenDauSach };
            SqlParameter sqlprMaTL = new SqlParameter("@MaTL", SqlDbType.VarChar, 15) { Value = dausach.TheLoai.MaTheLoai };
            return DatabaseManager.DbConnection.ExecuteStoredProcedureNonQuery(spName, sqlprThaoTac, sqlprMaDauSach, sqlprTenDauSach, sqlprMaTL);
        }
    }
}
